# -*- coding: utf-8 -*-
# authors 13077/13130
# data: 30 de outubro de 2014
#
from sqlalchemy import create_engine, null, text
from sqlalchemy.orm import sessionmaker
from xlrd import open_workbook
import database
import time


class DBHandler:
    """ Test class for SQL Alchemy """
    FILEPATH = '../data/IPC_Portugal_1977_2013.xls'
    DATABASEFILE = 'sqlite:///testalchemy.db'

    def __init__(self):
        t1 = time.time()
        engine = create_engine(self.DATABASEFILE, echo=False)
        sm = sessionmaker(bind=engine)
        self.session = sm()
        database.Base.metadata.drop_all(engine)
        database.Base.metadata.create_all(engine)
        self.read_data()
        print "DBHandler exec time: " + str(round(time.time() - t1, 3))
    def read_data(self):
        """
        Parses the xls file and populates the database
        :param filename: Absolute path of the file
        """

        workbook = open_workbook(self.FILEPATH)
        sheet = workbook.sheet_by_index(0)
        for row in range(1, sheet.nrows):
            list_row = []
            for col in range(1, sheet.ncols):
                value = sheet.cell(row, col).value
                if isinstance(value, float):
                    list_row.append(value)
                else:
                    list_row.append(null())
            self.session.add(database.IPC(list_row))
        self.session.commit()
        self.session.close()


    def query_interval(self, first_year, last_year):
        """
        Queries the database between years
        :param first_year: First year
        :param last_year: Last year
        :return: Query
        """
        t1 = time.time()
        query = self.session.query(database.IPC).filter(database.IPC.ano >= first_year).filter(
            database.IPC.ano <= last_year).all()
        self.session.close()
        print "query_interval exec time: " + str(round(time.time() - t1, 3))
        return query